/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webaccess.retailws.DAO;

import com.webaccess.retailws.Bean.DealBean;
import com.webaccess.retailws.Bean.ProductBean;
import com.webaccess.retailws.Delegate.ProductDelegate;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.webaccess.retailws.db.DBConnection;
import java.util.*;

/**
 *
 * @author Owner
 */
public class DealDAO {

    DBConnection dbc = null;
    Connection conn = null;
    ResultSet rs = null;
    String result = null;
    StringBuffer myQuery = null;
    DealBean dealBeanObj = null;
    ProductBean prodBean = null;
    ProductDelegate prodDelegate = null;

    public DealDAO() {
        try {
            dbc = new DBConnection();
            // conn = (Connection) dbc.getConnection();
            prodDelegate = new ProductDelegate();
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    public DealBean[] getAllDeal() {

        DealBean[] dealList = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectdeal('0')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    dealList = setAllDeal(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return dealList;
        }

    }

    private DealBean[] setAllDeal(ResultSet rs) {
        List alDeal = new ArrayList();
        try {
            alDeal.clear();
            while (rs.next()) {
                dealBeanObj = new DealBean();
                prodBean = new ProductBean();
                dealBeanObj.setDealId(rs.getLong("deal_id"));
                dealBeanObj.setDealName(rs.getString("dealname"));
                dealBeanObj.setStartDate(rs.getString("startdate"));
                dealBeanObj.setExpiryDate(rs.getString("enddate"));
                dealBeanObj.setDealPrice(rs.getDouble("dealprice"));
                dealBeanObj.setDealStatusId(rs.getLong("dealstatus_id"));
                dealBeanObj.setDealStatus(rs.getString("dealstatus"));
                prodBean = new ProductBean();
                prodBean.setProductId(rs.getLong("product_id"));
                prodBean.setProductName(rs.getString("productname"));
                prodBean.setProductBrand(rs.getString("productbrand"));
                prodBean.setProductBrandId(rs.getLong("productbrand_id"));
                prodBean.setProductCategoryId(rs.getLong("productcategory_id"));
                prodBean.setProductCategory(rs.getString("productcategory"));
                prodBean.setProductDescription(rs.getString("description"));
                prodBean.setProductDiscount(rs.getString("discount"));
                prodBean.setProductLargeImageName(rs.getString("prodlargeimage"));
                prodBean.setProductSmallImageName(rs.getString("prodsmallimage"));
                prodBean.setProductPrice(rs.getString("price"));
                prodBean.setProductQty(rs.getString("quantity"));
                prodBean.setProductStatus(rs.getString("status"));
                prodBean.setProductStatusId(rs.getLong("status_id"));
                prodBean.setProductTitle(rs.getString("producttitle"));
                prodBean.setProductTaxValue(rs.getString("tax"));
                prodBean.setProductTaxable(rs.getInt("taxable"));
                dealBeanObj.setProdBean(prodBean);
                alDeal.add(dealBeanObj);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (DealBean[]) alDeal.toArray(new DealBean[0]);
        }
    }

    public DealBean getDealDetails(Long dealId) {
        dealBeanObj = new DealBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectdeal('");
            myQuery.append(dealId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    dealBeanObj = setDealDetails(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return dealBeanObj;
        }
    }

    private DealBean setDealDetails(ResultSet rs) {
        try {
            while (rs.next()) {
                dealBeanObj = new DealBean();
                prodBean = new ProductBean();
                dealBeanObj.setDealId(rs.getLong("deal_id"));
                dealBeanObj.setDealName(rs.getString("dealname"));
                dealBeanObj.setStartDate(rs.getString("startdate"));
                dealBeanObj.setExpiryDate(rs.getString("enddate"));
                dealBeanObj.setDealPrice(rs.getDouble("dealprice"));
                dealBeanObj.setDealStatusId(rs.getLong("dealstatus_id"));
                dealBeanObj.setDealStatus(rs.getString("dealstatus"));
                prodBean = new ProductBean();
                prodBean.setProductId(rs.getLong("product_id"));
                prodBean.setProductName(rs.getString("productname"));
                prodBean.setProductBrand(rs.getString("productbrand"));
                prodBean.setProductBrandId(rs.getLong("productbrand_id"));
                prodBean.setProductCategoryId(rs.getLong("productcategory_id"));
                prodBean.setProductCategory(rs.getString("productcategory"));
                prodBean.setProductDescription(rs.getString("description"));
                prodBean.setProductDiscount(rs.getString("discount"));
                prodBean.setProductLargeImageName(rs.getString("prodlargeimage"));
                prodBean.setProductSmallImageName(rs.getString("prodsmallimage"));
                prodBean.setProductPrice(rs.getString("price"));
                prodBean.setProductQty(rs.getString("quantity"));
                prodBean.setProductStatus(rs.getString("status"));
                prodBean.setProductStatusId(rs.getLong("status_id"));
                prodBean.setProductTitle(rs.getString("producttitle"));
                prodBean.setProductTaxValue(rs.getString("tax"));
                prodBean.setProductTaxable(rs.getInt("taxable"));
                dealBeanObj.setProdBean(prodBean);

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return dealBeanObj;
        }
    }

    public boolean deleteDeal(Long dealId) {
        boolean isDealDeleted = false;
        rs = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_deletedeal('");
            myQuery.append(dealId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isDealDeleted = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isDealDeleted;
        }
    }
}
